﻿using MockPrjTeam1.Entity;
using SD.Commons.Helpers;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Data.SqlClient;
using System.Web;
using System.Data;

namespace MockPrjTeam1.DAO
{
    public class RegionDAO
    {
        public List<Region> GetActive()
        {
            var list = new List<Region>();

            var results = SqlHelper.ExecuteReader("GetActiveRegion");

            while (results.Read())
            {
                var item = new Region();
                item.ID = results.GetInt32(0);
                item.Name = results.GetString(1);
                item.Description = results.GetString(2);
                item.Country = results.GetString(6);
                item.Active = results.GetBoolean(4);
                list.Add(item);
            }

            return list;
        }

        public List<Region> GetAll()
        {
            var list = new List<Region>();

            var results = SqlHelper.ExecuteReader("GetAllRegion");

            while (results.Read())
            {
                var item = new Region();
                item.ID = results.GetInt32(0);
                item.Name = results.GetString(1);
                item.Description = results.GetString(2);
                item.Country = results.GetString(6);
                item.Active = results.GetBoolean(4);
                list.Add(item);
            }

            return list;
        }

        public Region GetOne(int id)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", id));

            var region = new Region();
            var results = SqlHelper.ExecuteReader("GetOneRegion", temp);
            if (results.Read())
            {
                region.ID = results.GetInt32(0);
                region.Name = results.GetString(1);
                region.Description = results.GetString(2);
                region.Country = results.GetString(6);
                region.Active = results.GetBoolean(4);
            }
            return region;
        }

        public bool CheckName(string name, int id)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", name));
            temp.Add(new SqlParameter("p2", id));
            var result = SqlHelper.ExecuteReader("CheckRegionName", temp);
            if (!result.Read()) 
                return true;
            else 
                return false;
        }

        public bool Edit(Region entity)
        {
            var temp1 = new Collection<SqlParameter>();
            temp1.Add(new SqlParameter("p1", entity.Country));
            var result1 = SqlHelper.ExecuteReader("GetCountryID", temp1);
            result1.Read();
            var countryID = result1.GetInt32(0);

            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p0", entity.ID));
            temp.Add(new SqlParameter("p1", entity.Name));
            temp.Add(new SqlParameter("p2", entity.Description));
            temp.Add(new SqlParameter("p3", countryID));
            temp.Add(new SqlParameter("p4", entity.Active));
            var result = SqlHelper.ExecuteNonQuery("EditRegion", temp);
            return true;
        }

        public bool Create(Region entity)
        {
            int countryID = 0;
            var temp1 = new Collection<SqlParameter>();
            temp1.Add(new SqlParameter("p1", entity.Country));
            var result1 = SqlHelper.ExecuteReader("GetCountryID", temp1);
            if (result1.Read())
                countryID = result1.GetInt32(0);

            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", entity.Name));
            temp.Add(new SqlParameter("p2", entity.Description));
            temp.Add(new SqlParameter("p3", countryID));
            temp.Add(new SqlParameter("p4", entity.Active));
            var result = SqlHelper.ExecuteNonQuery("CreateRegion", temp);
            return true;
        }

        public string GetRegionName(int regionId)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", regionId));
            var result = SqlHelper.ExecuteReader("GetRegionName", temp);
            if (result.Read())
                return result.GetString(0);
            return null;
        }

        public int GetRegionID(string regionName)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", regionName));
            var result = SqlHelper.ExecuteReader("GetRegionID", temp);
            if (result.Read())
                return result.GetInt32(0);
            return 0;
        }

        public List<Region> Filter(int filterId, int isActive)
        {
            var list = new List<Region>();

            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", filterId));
            temp.Add(new SqlParameter("p2", isActive));

            var results = SqlHelper.ExecuteReader("FilterRegion", temp);

            while (results.Read())
            {
                var item = new Region();
                item.ID = results.GetInt32(0);
                item.Name = results.GetString(1);
                item.Description = results.GetString(2);
                item.Country = results.GetString(6);
                item.Active = results.GetBoolean(4);
                list.Add(item);
            }

            return list;
        }
    }
}